﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class ZHCX_YSKXZLFXB : System.Web.UI.Page
{
  public DataSet dsWBDW = null, dsNBDW = null, dsJYDW = null;
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!Page.IsPostBack)
    {
      DataSet ds = CBSJ.GetYear();
      CommonUse.BindDDL(ds, "year", "year", DateTime.Now.Year.ToString(), ddlYear);
      ddlMonth.SelectedValue = DateTime.Now.ToString("yyyy-MM").Substring(5, 2);
    }
    GetData();
  }

  protected void GetData()
  {

    string sql = @"select a.khid cx_khbm,a.khmc,a.ssfy dq,b.ssfy bn,c.ssfy yn,d.ssfy en,e.ssfy sn,f.ssfy esn  
from 
(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY,c.khmc from 
 (select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
  WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 and
   convert(varchar(7),CBSJ,23)<='{0}' group by b.khid) a left join 
 (select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a 
  where convert(varchar(7),jfSJ,23)<='{0}' group by a.khid) b
  on a.khid=b.khid
  join jcxx_khxx c on a.khid=c.khid
where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0 and c.ic='否' {6}) a
left join 
(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY from 
 (select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
  WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 and
   convert(varchar(7),CBSJ,23)>'{1}' and convert(varchar(7),CBSJ,23)<='{0}' group by b.khid) a left join 
 (select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a 
  where convert(varchar(7),jfSJ,23)>'{1}' and convert(varchar(7),jfSJ,23)<='{0}' group by a.khid) b
  on a.khid=b.khid
where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0) b
on a.khid=b.khid
left join 
(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY from 
 (select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
  WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 and
   convert(varchar(7),CBSJ,23)>'{2}' and convert(varchar(7),CBSJ,23)<='{1}' group by b.khid) a left join 
 (select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a 
  where convert(varchar(7),jfSJ,23)>'{2}' and convert(varchar(7),jfSJ,23)<='{1}' group by a.khid) b
  on a.khid=b.khid
where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0) c
on a.khid=c.khid
left join 
(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY from 
 (select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
  WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 and
   convert(varchar(7),CBSJ,23)>'{3}' and convert(varchar(7),CBSJ,23)<='{2}' group by b.khid) a left join 
 (select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a 
  where convert(varchar(7),jfSJ,23)>'{3}' and convert(varchar(7),jfSJ,23)<='{2}' group by a.khid) b
  on a.khid=b.khid
where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0) d
on a.khid=d.khid
left join 
(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY from 
 (select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
  WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 and
   convert(varchar(7),CBSJ,23)>'{4}' and convert(varchar(7),CBSJ,23)<='{3}' group by b.khid) a left join 
 (select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a 
  where convert(varchar(7),jfSJ,23)>'{4}' and convert(varchar(7),jfSJ,23)<='{3}' group by a.khid) b
  on a.khid=b.khid
where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0) e
on a.khid=e.khid
left join 
(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY from 
 (select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
  WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 and
   convert(varchar(7),CBSJ,23)<='{4}' group by b.khid) a left join 
 (select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a 
  where convert(varchar(7),jfSJ,23)<='{4}' group by a.khid) b
  on a.khid=b.khid
where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0) f
on a.khid=f.khid";
    string DQ = Convert.ToDateTime(ddlYear.SelectedValue + "-" + ddlMonth.SelectedValue).ToString("yyyy-MM");
    string BN = Convert.ToDateTime(ddlYear.SelectedValue + "-" + ddlMonth.SelectedValue).AddMonths(-6).ToString("yyyy-MM");
    string YN = Convert.ToDateTime(ddlYear.SelectedValue + "-" + ddlMonth.SelectedValue).AddYears(-1).ToString("yyyy-MM");
    string EN = Convert.ToDateTime(ddlYear.SelectedValue + "-" + ddlMonth.SelectedValue).AddYears(-2).ToString("yyyy-MM");
    string SN = Convert.ToDateTime(ddlYear.SelectedValue + "-" + ddlMonth.SelectedValue).AddYears(-3).ToString("yyyy-MM");
    string ESN = Convert.ToDateTime(ddlYear.SelectedValue + "-" + ddlMonth.SelectedValue).AddYears(-20).ToString("yyyy-MM");
    //Response.Write(BN);, "and b.kh='内结上市'"
    string s = string.Format(sql, DQ, BN, YN, EN, SN, ESN, "and c.kh<>'内结上市' and c.kh<>'内部存续'");
    dsWBDW = DbHelperSQL.Query(s);

    s = string.Format(sql, DQ, BN, YN, EN, SN, ESN, "and c.kh='内结上市'");
    dsNBDW = DbHelperSQL.Query(s);

    s = string.Format(sql, DQ, BN, YN, EN, SN, ESN, "and c.kh='内部存续'");
    dsJYDW = DbHelperSQL.Query(s);
    //Response.Write(sql);
    decimal wb = dsWBDW.Tables[0].Compute("sum(dq)", "") == DBNull.Value ? 0 : Convert.ToDecimal(dsWBDW.Tables[0].Compute("sum(dq)", ""));
    decimal nb = dsNBDW.Tables[0].Compute("sum(dq)", "") == DBNull.Value ? 0 : Convert.ToDecimal(dsNBDW.Tables[0].Compute("sum(dq)", ""));
    decimal jy = dsJYDW.Tables[0].Compute("sum(dq)", "") == DBNull.Value ? 0 : Convert.ToDecimal(dsJYDW.Tables[0].Compute("sum(dq)", ""));
    lbHJ.Text = (wb + nb + jy).ToString();
  }

  protected void btnQuery_Click(object sender, EventArgs e)
  {

  }
}